<?php
$dsn = 'mysql:host=localhost;port=3306;dbname=zgdb;charset=UTF8';
$usr = 'root';
$pwd = '';
$opt = [19 => 2, 1002 => 'set names utf8'];
try {
    $d = new pdo($dsn, $usr, $pwd, $opt);
} catch (PDOException $e) {
    die($e->getMessage());
}

function conn() {
    global $d;
    return $d;
}

function insert(string $sql, array $param): bool|int {
    global $d;
    $st = $d->prepare($sql);
    $result = $st->execute($param);
    $st->closeCursor();
    return $result;
}

function update(string $sql, array $param): bool|int {
    return insert($sql, $param);
}

function delete(string $sql, array $param): bool|int {
    return insert($sql, $param);
}

function select(string $sql, array $param): array|null {
    global $d;
    $st = $d->prepare($sql);
    $st->execute($param);
    $result = $st->fetchAll();
    return $result;
}

function page($sql, $param, $currpage = 1, $pagesize = 10): array|bool {
    global $d;
    $recordcount = scount($sql, $param);
    if ($recordcount < 1) return false;
    $pagecount = ceil($recordcount / $pagesize);
    if ($currpage < 1) $currpage = 1;
    if ($currpage > $pagecount) $currpage = $pagecount;
    $st = $d->prepare($sql . ' limit :start,:size');
    $start = $currpage * $pagesize - $pagesize;
    $size = $pagesize;

    foreach ($param as $key => $value) {
        $st->bindValue(':' . $key, $value);
    }
    $st->bindParam('start', $start, PDO::PARAM_INT);
    $st->bindParam('size', $size, PDO::PARAM_INT);
    $st->execute();
    $rs = $st->fetchAll();
    $st->closeCursor();
    $pageinfo = '<div class="page">';
    if ($currpage > 1) $pageinfo .= sprintf("<a href='?p=%d'>上一页</a>", $currpage - 1);
    $ss = 1;
    $ee = 10;
    if ($currpage >= 7) {
        $ss = $currpage - 5;
        $ee = $currpage + 4;
    }
    foreach (range($ss, $ee) as $value) {
        if ($value > $pagecount) break;
        if ($currpage == $value) {
            $pageinfo .= sprintf("<span >%d</span>", $value);
        } else {
            $pageinfo .= sprintf("<a href='?p=%d'>%d</a>", $value, $value);
        }
    }
    if ($currpage < $pagecount) $pageinfo .= sprintf("<a href='?p=%d'>下一页</a>", $currpage + 1);
    $pageinfo .= '</div>';
    return ['pageinfo' => $pageinfo, 'data' => $rs, 'page' => ['currpage' => $currpage, 'pagesize' => $pagesize, 'pagecount' => $pagecount, 'recordcount' => $recordcount]];
}

function query($querystring): false|PDOStatement {
    global $d;
    return $d->query($querystring);
}

function execute($updatestring): false|int {
    global $d;
    return $d->exec($updatestring);
}

function dump($array) {
    echo '<pre>';
    print_r($array);
    echo '</pre>';
}

function scount($sql, $param = null) {
    global $d;
    $st = $d->prepare('select count(*) ' . substr($sql, strpos($sql, 'from')));
    if (isset($param)) {
        $st->execute($param);
    } else {
        $st->execute();
    }
    $num = array_pop($st->fetchAll()[0]);
    $st->closeCursor();
    return $num;
}

function dbinfo() {

}

function dver() {
    global $d;
    foreach ($d->query("select @@version") as $value) {
        return array_pop($value);
    }
}

function tbs(string $tname = null): array {
    $tbs = [];
    foreach (isset($tname) ? query('show tables from ' . $tname) : query('show tables') as $rows) {
        $tbs[] = array_pop($rows);
    }
    return $tbs;
}

function dbs(): array {
    $dbs = [];
    foreach (query('show databases') as $rows) {
        $dbs[] = array_pop($rows);
    }
    return $dbs;
}